﻿
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Nick.Wang
-- Create date: 20180920
-- Description:	营业结算数据
-- =============================================
CREATE PROCEDURE P_SettlementReport
@Num INT,
@day DATETIME,
@beginTime varchar(100),
 @enID INT
AS
BEGIN
DECLARE @endTime varchar(100);
SET @endTime=CONVERT(varchar(100),DATEADD(DAY, convert(int, 1), @day),20);
DECLARE @t TABLE(
order_id VARCHAR(50),
Wxuser_name VARCHAR(50),
car_no VARCHAR(50),
pay_fee DECIMAL(8,2),
UnionPay_fee DECIMAL(8,2),
Weixin_fee DECIMAL(8,2),
Alipay_fee DECIMAL(8,2),
Tonglian_fee DECIMAL(8,2),
 XJPay_fee DECIMAL(8,2),
  Enterprise_Name VARCHAR(50),
  CCEnterprise_Name VARCHAR(50),
 pay_time DATETIME,
 create_time DATETIME
);
DECLARE @A TABLE (
ID INT  IDENTITY   (1,   1)   NOT   NULL ,
CCEnterprise_ID INT,
CCEnterprise_Name VARCHAR(50),
pay_fee DECIMAL(8,2),
UnionPay_fee DECIMAL(8,2),
Weixin_fee DECIMAL(8,2),
Alipay_fee DECIMAL(8,2),
Tonglian_fee DECIMAL(8,2),
XJPay_fee DECIMAL(8,2),
Riqi DATETIME
)
DECLARE @CCEnterprise_Name VARCHAR(50);
SELECT @CCEnterprise_Name=Enterprise_Name FROM  dbo.Enterprise WHERE ID=@enID
INSERT @t( order_id ,Wxuser_name ,car_no , pay_fee ,UnionPay_fee ,Weixin_fee , Alipay_fee ,Tonglian_fee ,XJPay_fee ,Enterprise_Name ,CCEnterprise_Name ,pay_time , create_time)
 EXEC dbo.P_ORDER_Report @beginTime,@endTime,@enID ; 
DECLARE @i INT;
SET @i=1;
WHILE  @i<=@Num
BEGIN
INSERT @A (CCEnterprise_ID ,CCEnterprise_Name , pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee , XJPay_fee , Riqi 
        )
SELECT @enID, @CCEnterprise_Name, SUM(pay_fee) pay_fee,SUM(UnionPay_fee) UnionPay_fee,SUM(Weixin_fee) Weixin_fee,SUM(Alipay_fee) Alipay_fee,SUM(Tonglian_fee
) Tonglian_fee,SUM(XJPay_fee) XJPay_fee,CONVERT(varchar(100),DATEADD(DAY, convert(int, -@Num+@i), @day),20) Riqi  FROM @t WHERE pay_time>=CONVERT(varchar(100),DATEADD(DAY, convert(int, -@Num+@i), @day),20) AND pay_time<=CONVERT(varchar(100),DATEADD(DAY, convert(int, -@Num+@i+1), @day),20);
SET @i+=1;
END
SELECT * FROM @A

END
GO

INSERT dbo.Permission
        ( Name, Type, Route )
VALUES  ( '结算报表', -- Name - varchar(50)
          5, -- Type - int
          '/Report/SettlementReport'  -- Route - varchar(100)
          )
UPDATE dbo.Role SET PermissionID='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34' WHERE ID=1